#!/usr/bin/env python3
"""
Export existing database to CSV
"""

import sqlite3
import csv
import json

def export_to_csv():
    """Export database to CSV file"""
    conn = sqlite3.connect('un_resolutions.db')
    cursor = conn.cursor()

    cursor.execute('''
        SELECT resolution_number, title, date, topic_category, votes_for, votes_against,
               votes_abstain, is_lopsided, minority_countries, minority_count,
               majority_side, url, abstain_countries
        FROM resolutions
        ORDER BY date DESC
    ''')

    with open('un_resolutions.csv', 'w', newline='', encoding='utf-8') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow(['Resolution Number', 'Title', 'Date', 'Topic Category',
                       'Votes For', 'Votes Against', 'Votes Abstain', 'Is Lopsided',
                       'Minority Countries', 'Minority Count', 'Majority Side', 'URL',
                       'Abstain Countries', 'Abstain Countries Count'])

        for row in cursor.fetchall():
            # Parse minority countries from JSON
            minority_countries = json.loads(row[8]) if row[8] and isinstance(row[8], str) else []
            minority_countries_str = '; '.join(minority_countries)

            # Parse abstain countries from JSON
            abstain_countries = json.loads(row[12]) if row[12] and isinstance(row[12], str) else []
            abstain_countries_str = '; '.join(abstain_countries)
            abstain_countries_count = len(abstain_countries)

            writer.writerow([
                row[0], row[1], row[2], row[3], row[4], row[5],
                row[6], row[7], minority_countries_str, row[9], row[10], row[11],
                abstain_countries_str, abstain_countries_count
            ])

    conn.close()
    print("Exported lopsided resolutions to un_resolutions.csv")

if __name__ == "__main__":
    export_to_csv()